{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "import datetime\n",
    "from datetime import timedelta\n",
    "\n",
    "file_path=\"~/Documents/Data/Full_MIMIC/\"\n",
    "\n",
    "pd.set_option('display.max_rows', 150)\n",
    "pd.set_option('display.max_columns', 300)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "adm=pd.read_csv(file_path+\"Admissions_processed.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>DOBTIME</th>\n",
       "      <th>ROW_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>ADMITTIME</th>\n",
       "      <th>DISCHTIME</th>\n",
       "      <th>DEATHTIME</th>\n",
       "      <th>ADMISSION_TYPE</th>\n",
       "      <th>ADMISSION_LOCATION</th>\n",
       "      <th>DISCHARGE_LOCATION</th>\n",
       "      <th>INSURANCE</th>\n",
       "      <th>LANGUAGE</th>\n",
       "      <th>RELIGION</th>\n",
       "      <th>MARITAL_STATUS</th>\n",
       "      <th>ETHNICITY</th>\n",
       "      <th>EDREGTIME</th>\n",
       "      <th>EDOUTTIME</th>\n",
       "      <th>DIAGNOSIS</th>\n",
       "      <th>HOSPITAL_EXPIRE_FLAG</th>\n",
       "      <th>HAS_CHARTEVENTS_DATA</th>\n",
       "      <th>ELAPSED_TIME</th>\n",
       "      <th>ELAPSED_DAYS</th>\n",
       "      <th>DEATHTAG</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>250</td>\n",
       "      <td>2164-12-27 00:00:00</td>\n",
       "      <td>324</td>\n",
       "      <td>124271</td>\n",
       "      <td>2188-11-12 09:22:00</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "      <td>2188-11-22 12:00:00</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>DEAD/EXPIRED</td>\n",
       "      <td>Self Pay</td>\n",
       "      <td>HAIT</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>BLACK/AFRICAN AMERICAN</td>\n",
       "      <td>2188-11-12 06:56:00</td>\n",
       "      <td>2188-11-12 10:10:00</td>\n",
       "      <td>PNEUMONIA;R/O TB</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>10 days 02:38:00.000000000</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7</td>\n",
       "      <td>253</td>\n",
       "      <td>2089-11-26 00:00:00</td>\n",
       "      <td>328</td>\n",
       "      <td>176189</td>\n",
       "      <td>2174-01-21 20:58:00</td>\n",
       "      <td>2174-01-26 16:15:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>TRANSFER FROM HOSP/EXTRAM</td>\n",
       "      <td>SNF</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>WIDOWED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>COMPLETE HEART BLOCK\\PACEMAKER IMPLANT</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4 days 19:17:00.000000000</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>255</td>\n",
       "      <td>2109-08-05 00:00:00</td>\n",
       "      <td>329</td>\n",
       "      <td>112013</td>\n",
       "      <td>2187-02-12 10:30:00</td>\n",
       "      <td>2187-02-15 10:30:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ELECTIVE</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>NOT SPECIFIED</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>BENIGN PROSTATIC HYPERTROPHY/SDA</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3 days 00:00:00.000000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>16</td>\n",
       "      <td>261</td>\n",
       "      <td>2025-08-04 00:00:00</td>\n",
       "      <td>337</td>\n",
       "      <td>118523</td>\n",
       "      <td>2101-12-27 01:50:00</td>\n",
       "      <td>2102-01-12 13:39:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>EMERGENCY</td>\n",
       "      <td>EMERGENCY ROOM ADMIT</td>\n",
       "      <td>DISC-TRAN CANCER/CHLDRN H</td>\n",
       "      <td>Medicare</td>\n",
       "      <td>NaN</td>\n",
       "      <td>OTHER</td>\n",
       "      <td>MARRIED</td>\n",
       "      <td>WHITE</td>\n",
       "      <td>2101-12-26 20:11:00</td>\n",
       "      <td>2101-12-27 01:50:00</td>\n",
       "      <td>HYPOXIA</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>16 days 11:49:00.000000000</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>17</td>\n",
       "      <td>262</td>\n",
       "      <td>2090-01-05 00:00:00</td>\n",
       "      <td>338</td>\n",
       "      <td>106019</td>\n",
       "      <td>2153-09-25 18:01:00</td>\n",
       "      <td>2153-09-28 18:48:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ELECTIVE</td>\n",
       "      <td>PHYS REFERRAL/NORMAL DELI</td>\n",
       "      <td>HOME</td>\n",
       "      <td>Private</td>\n",
       "      <td>ENGL</td>\n",
       "      <td>CATHOLIC</td>\n",
       "      <td>SINGLE</td>\n",
       "      <td>UNKNOWN/NOT SPECIFIED</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>HYPERTROPHIC CARDIOMYOPATHY\\ETHANOL SEPTAL ABL...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>3 days 00:47:00.000000000</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  SUBJECT_ID              DOBTIME  ROW_ID  HADM_ID  \\\n",
       "0           3         250  2164-12-27 00:00:00     324   124271   \n",
       "1           7         253  2089-11-26 00:00:00     328   176189   \n",
       "2           8         255  2109-08-05 00:00:00     329   112013   \n",
       "3          16         261  2025-08-04 00:00:00     337   118523   \n",
       "4          17         262  2090-01-05 00:00:00     338   106019   \n",
       "\n",
       "             ADMITTIME            DISCHTIME            DEATHTIME  \\\n",
       "0  2188-11-12 09:22:00  2188-11-22 12:00:00  2188-11-22 12:00:00   \n",
       "1  2174-01-21 20:58:00  2174-01-26 16:15:00                  NaN   \n",
       "2  2187-02-12 10:30:00  2187-02-15 10:30:00                  NaN   \n",
       "3  2101-12-27 01:50:00  2102-01-12 13:39:00                  NaN   \n",
       "4  2153-09-25 18:01:00  2153-09-28 18:48:00                  NaN   \n",
       "\n",
       "  ADMISSION_TYPE         ADMISSION_LOCATION         DISCHARGE_LOCATION  \\\n",
       "0      EMERGENCY       EMERGENCY ROOM ADMIT               DEAD/EXPIRED   \n",
       "1      EMERGENCY  TRANSFER FROM HOSP/EXTRAM                        SNF   \n",
       "2       ELECTIVE  PHYS REFERRAL/NORMAL DELI                       HOME   \n",
       "3      EMERGENCY       EMERGENCY ROOM ADMIT  DISC-TRAN CANCER/CHLDRN H   \n",
       "4       ELECTIVE  PHYS REFERRAL/NORMAL DELI                       HOME   \n",
       "\n",
       "  INSURANCE LANGUAGE       RELIGION MARITAL_STATUS               ETHNICITY  \\\n",
       "0  Self Pay     HAIT  NOT SPECIFIED         SINGLE  BLACK/AFRICAN AMERICAN   \n",
       "1  Medicare      NaN       CATHOLIC        WIDOWED                   WHITE   \n",
       "2  Medicare     ENGL  NOT SPECIFIED        MARRIED                   WHITE   \n",
       "3  Medicare      NaN          OTHER        MARRIED                   WHITE   \n",
       "4   Private     ENGL       CATHOLIC         SINGLE   UNKNOWN/NOT SPECIFIED   \n",
       "\n",
       "             EDREGTIME            EDOUTTIME  \\\n",
       "0  2188-11-12 06:56:00  2188-11-12 10:10:00   \n",
       "1                  NaN                  NaN   \n",
       "2                  NaN                  NaN   \n",
       "3  2101-12-26 20:11:00  2101-12-27 01:50:00   \n",
       "4                  NaN                  NaN   \n",
       "\n",
       "                                           DIAGNOSIS  HOSPITAL_EXPIRE_FLAG  \\\n",
       "0                                   PNEUMONIA;R/O TB                     1   \n",
       "1             COMPLETE HEART BLOCK\\PACEMAKER IMPLANT                     0   \n",
       "2                   BENIGN PROSTATIC HYPERTROPHY/SDA                     0   \n",
       "3                                            HYPOXIA                     0   \n",
       "4  HYPERTROPHIC CARDIOMYOPATHY\\ETHANOL SEPTAL ABL...                     0   \n",
       "\n",
       "   HAS_CHARTEVENTS_DATA                ELAPSED_TIME  ELAPSED_DAYS  DEATHTAG  \n",
       "0                     1  10 days 02:38:00.000000000            10         1  \n",
       "1                     1   4 days 19:17:00.000000000             4         0  \n",
       "2                     1   3 days 00:00:00.000000000             3         0  \n",
       "3                     1  16 days 11:49:00.000000000            16         0  \n",
       "4                     1   3 days 00:47:00.000000000             3         0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "adm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We now consider the labevents dataset. We select only the patients with the same criteria as above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of patients remaining in the database: \n",
      "23418\n"
     ]
    }
   ],
   "source": [
    "lab=pd.read_csv(file_path+\"LABEVENTS.csv\")\n",
    "\n",
    "#Restrict the dataset to the previously selected admission ids only.\n",
    "adm_ids=list(adm[\"HADM_ID\"])\n",
    "lab=lab.loc[lab[\"HADM_ID\"].isin(adm_ids)]\n",
    "\n",
    "print(\"Number of patients remaining in the database: \")\n",
    "print(lab[\"SUBJECT_ID\"].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We load the D_ITEMS dataframe which contains the name of the ITEMID. And we merge both tables together."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of patients remaining in the database: \n",
      "23418\n"
     ]
    }
   ],
   "source": [
    "#item_id\n",
    "item_id=pd.read_csv(file_path+\"D_LABITEMS.csv\")\n",
    "item_id_1=item_id[[\"ITEMID\",\"LABEL\"]]\n",
    "item_id_1.head()\n",
    "\n",
    "#We merge the name of the item administrated.\n",
    "lab2=pd.merge(lab,item_id_1,on=\"ITEMID\")\n",
    "lab2.head()\n",
    "print(\"Number of patients remaining in the database: \")\n",
    "print(lab2[\"SUBJECT_ID\"].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of patients remaining in the database: \n",
      "23418\n"
     ]
    }
   ],
   "source": [
    "n_best=150\n",
    "#For each item, evaluate the number of patients who have been given this item.\n",
    "pat_for_item=lab2.groupby(\"LABEL\")[\"SUBJECT_ID\"].nunique()\n",
    "#Order by occurence and take the 20 best (the ones with the most patients)\n",
    "frequent_labels=pat_for_item.sort_values(ascending=False)[:n_best]\n",
    "\n",
    "#Select only the time series with high occurence.\n",
    "lab3=lab2.loc[lab2[\"LABEL\"].isin(list(frequent_labels.index))].copy()\n",
    "\n",
    "print(\"Number of patients remaining in the database: \")\n",
    "print(lab3[\"SUBJECT_ID\"].nunique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Units Cleaning\n",
    "\n",
    "#### 1) In amounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LABEL                                VALUEUOM\n",
      "% Hemoglobin A1c                     %             4484\n",
      "Acetaminophen                        ug/mL         3507\n",
      "Alanine Aminotransferase (ALT)       IU/L         56771\n",
      "Albumin                              g/dL         37366\n",
      "Alkaline Phosphatase                 IU/L         54975\n",
      "Alveolar-arterial Gradient           mm Hg         7446\n",
      "Amylase                              IU/L         20973\n",
      "Anion Gap                            mEq/L       250362\n",
      "Asparate Aminotransferase (AST)      IU/L         56708\n",
      "Atypical Lymphocytes                 %            12255\n",
      "Bands                                %            16712\n",
      "Base Excess                          mEq/L       223138\n",
      "Basophils                            %            38033\n",
      "Bicarbonate                          mEq/L       256363\n",
      "Bilirubin                            EU/dL        17596\n",
      "                                     mg/dL        11891\n",
      "Bilirubin, Direct                    mg/dL         4619\n",
      "Bilirubin, Indirect                  mg/dL         4339\n",
      "Bilirubin, Total                     mg/dL        56964\n",
      "Blood                                mg/dL         5688\n",
      "CK-MB Index                          %            10557\n",
      "Calcium, Total                       mg/dL       196865\n",
      "Calculated Bicarbonate, Whole Blood  mEq/L         4013\n",
      "Calculated Total CO2                 mEq/L       213439\n",
      "                                     MEQ/L         9687\n",
      "Chloride                             mEq/L       263518\n",
      "Chloride, Urine                      mEq/L         3727\n",
      "Chloride, Whole Blood                mEq/L        24036\n",
      "Cholesterol Ratio (Total/HDL)        Ratio         3702\n",
      "Cholesterol, HDL                     mg/dL         3722\n",
      "Cholesterol, LDL, Calculated         mg/dL         3508\n",
      "Cholesterol, Total                   mg/dL         3988\n",
      "Cortisol                             ug/dL         4386\n",
      "Creatine Kinase (CK)                 IU/L         44416\n",
      "Creatine Kinase, MB Isoenzyme        ng/mL        39424\n",
      "Creatinine                           mg/dL       265620\n",
      "Creatinine, Urine                    mg/dL         9294\n",
      "D-Dimer                              ng/mL         1778\n",
      "Eosinophils                          %            38634\n",
      "Epithelial Cells                     #/hpf        26253\n",
      "Ethanol                              mg/dL         3389\n",
      "Ferritin                             ng/mL         3967\n",
      "                                     ng/ml          315\n",
      "Fibrinogen, Functional               mg/dL        19195\n",
      "Folate                               ng/mL         2215\n",
      "Free Calcium                         mmol/L      121988\n",
      "Glucose                              mg/dL       387800\n",
      "Granular Casts                       #/lpf         2427\n",
      "Haptoglobin                          mg/dL         3428\n",
      "                                     MG/DL          360\n",
      "Hematocrit                           %           300108\n",
      "Hematocrit, Calculated               %            50143\n",
      "Hemoglobin                           g/dL        296779\n",
      "Hyaline Casts                        #/lpf         5114\n",
      "Iron                                 ug/dL         4472\n",
      "Iron Binding Capacity, Total         ug/dL         4301\n",
      "Ketone                               mg/dL        30453\n",
      "Lactate                              mmol/L       79064\n",
      "Lactate Dehydrogenase (LD)           IU/L         30548\n",
      "Lipase                               IU/L         21161\n",
      "Lymphocytes                          %            40360\n",
      "MCH                                  pg          244834\n",
      "MCHC                                 %           244894\n",
      "MCV                                  fL          244832\n",
      "Magnesium                            mg/dL       237650\n",
      "Metamyelocytes                       %            12092\n",
      "Monocytes                            %            40228\n",
      "Myelocytes                           %            12070\n",
      "NTproBNP                             pg/mL         1666\n",
      "Neutrophils                          %            37915\n",
      "O2 Flow                              L/min         5165\n",
      "Osmolality, Measured                 mOsm/kg       8693\n",
      "                                     MOSM/KG        451\n",
      "                                     MOSM/L         224\n",
      "Osmolality, Urine                    mOsm/kg       6800\n",
      "Oxygen                               %            29037\n",
      "Oxygen Saturation                    %            68696\n",
      "PT                                   sec         149786\n",
      "                                     SECONDS      12629\n",
      "PTT                                  sec         173953\n",
      "Phenytoin                            ug/mL        10112\n",
      "Phosphate                            mg/dL       198645\n",
      "Platelet Count                       K/uL        254753\n",
      "Polys                                %             3769\n",
      "Potassium                            mEq/L       280697\n",
      "Potassium, Urine                     mEq/L         4162\n",
      "Potassium, Whole Blood               mEq/L       102517\n",
      "Protein                              mg/dL        32217\n",
      "Protein, Total                       g/dL          1733\n",
      "RBC                                  #/hpf        26669\n",
      "RDW                                  %           244318\n",
      "Red Blood Cells                      m/uL        244837\n",
      "Reticulocyte Count, Automated        %             2540\n",
      "Salicylate                           mg/dL         3229\n",
      "Sedimentation Rate                   mm/hr         1574\n",
      "Sodium                               mEq/L       267473\n",
      "Sodium, Urine                        mEq/L         9101\n",
      "Sodium, Whole Blood                  mEq/L        38621\n",
      "Specific Gravity                                  36412\n",
      "Thyroid Stimulating Hormone          uIU/mL        5654\n",
      "                                     uU/ML          720\n",
      "Transferrin                          mg/dL         4313\n",
      "Transitional Epithelial Cells        #/hpf         1579\n",
      "Triglycerides                        mg/dL         4705\n",
      "                                     MG/DL          855\n",
      "Troponin I                           ng/ml         4259\n",
      "Troponin T                           ng/mL        23457\n",
      "                                     ng/ml         3726\n",
      "Urea Nitrogen                        mg/dL       264389\n",
      "Urea Nitrogen, Urine                 mg/dL         4505\n",
      "Uric Acid                            mg/dL         3739\n",
      "Urobilinogen                         mg/dL        19523\n",
      "                                     EU/dL        10960\n",
      "Vancomycin                           ug/mL        15205\n",
      "Vitamin B12                          pg/mL         3045\n",
      "WBC                                  #/hpf        26856\n",
      "White Blood Cells                    K/uL        246727\n",
      "pCO2                                 mm Hg       213504\n",
      "                                     MM HG         9602\n",
      "pH                                   units       269235\n",
      "                                     UNITS        11404\n",
      "pO2                                  mm Hg       213521\n",
      "                                     MM HG         9602\n",
      "Name: VALUEUOM, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "#Verification that all input labels have the same amounts units.\n",
    "print(lab3.groupby(\"LABEL\")[\"VALUEUOM\"].value_counts())\n",
    "\n",
    "#Correct the units\n",
    "lab3.loc[lab3[\"LABEL\"]==\"Calculated Total CO2\",\"VALUEUOM\"]=\"mEq/L\"\n",
    "lab3.loc[lab3[\"LABEL\"]==\"PT\",\"VALUEUOM\"]=\"sec\"\n",
    "lab3.loc[lab3[\"LABEL\"]==\"pCO2\",\"VALUEUOM\"]=\"mm Hg\"\n",
    "lab3.loc[lab3[\"LABEL\"]==\"pH\",\"VALUEUOM\"]=\"units\"\n",
    "lab3.loc[lab3[\"LABEL\"]==\"pO2\",\"VALUEUOM\"]=\"mm Hg\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Only select the subset that was used in the paper (only missing is INR(PT))\n",
    "subset=[\"Albumin\",\"Alanine Aminotransferase (ALT)\",\"Alkaline Phosphatase\",\"Anion Gap\",\"Asparate Aminotransferase (AST)\",\"Base Excess\",\"Basophils\",\"Bicarbonate\",\"Bilirubin, Total\",\"Calcium, Total\",\"Calculated Total CO2\",\"Chloride\",\"Creatinine\",\"Eosinophils\",\"Glucose\",\"Hematocrit\",\"Hemoglobin\",\n",
    "\"Lactate\",\"Lymphocytes\",\"MCH\",\"MCHC\",\"MCV\",\"Magnesium\",\"Monocytes\",\"Neutrophils\",\"PT\",\"PTT\",\"Phosphate\",\"Platelet Count\",\"Potassium\",\"RDW\",\"Red Blood Cells\",\"Sodium\",\"Specific Gravity\",\"Urea Nitrogen\",\"White Blood Cells\",\"pCO2\",\"pH\",\"pO2\"]\n",
    "\n",
    "lab3=lab3.loc[lab3[\"LABEL\"].isin(subset)].copy()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Check for outliers\n",
    "\n",
    "#### 1) In amounts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LABEL</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Alanine Aminotransferase (ALT)</th>\n",
       "      <td>56767.0</td>\n",
       "      <td>220.221912</td>\n",
       "      <td>818.161372</td>\n",
       "      <td>0.000</td>\n",
       "      <td>21.00</td>\n",
       "      <td>40.000</td>\n",
       "      <td>97.000</td>\n",
       "      <td>25460.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Albumin</th>\n",
       "      <td>37358.0</td>\n",
       "      <td>3.035668</td>\n",
       "      <td>0.695180</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2.50</td>\n",
       "      <td>3.000</td>\n",
       "      <td>3.500</td>\n",
       "      <td>6.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Alkaline Phosphatase</th>\n",
       "      <td>54975.0</td>\n",
       "      <td>140.230341</td>\n",
       "      <td>165.232238</td>\n",
       "      <td>0.000</td>\n",
       "      <td>66.00</td>\n",
       "      <td>94.000</td>\n",
       "      <td>151.000</td>\n",
       "      <td>4695.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Anion Gap</th>\n",
       "      <td>250378.0</td>\n",
       "      <td>13.503702</td>\n",
       "      <td>3.638658</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>11.00</td>\n",
       "      <td>13.000</td>\n",
       "      <td>15.000</td>\n",
       "      <td>67.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Asparate Aminotransferase (AST)</th>\n",
       "      <td>56708.0</td>\n",
       "      <td>243.030472</td>\n",
       "      <td>1050.164745</td>\n",
       "      <td>0.000</td>\n",
       "      <td>26.00</td>\n",
       "      <td>48.000</td>\n",
       "      <td>106.000</td>\n",
       "      <td>36400.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Base Excess</th>\n",
       "      <td>223110.0</td>\n",
       "      <td>-0.015864</td>\n",
       "      <td>4.870077</td>\n",
       "      <td>-413.000</td>\n",
       "      <td>-2.00</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.000</td>\n",
       "      <td>162.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Basophils</th>\n",
       "      <td>38033.0</td>\n",
       "      <td>0.296372</td>\n",
       "      <td>0.486295</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.400</td>\n",
       "      <td>40.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bicarbonate</th>\n",
       "      <td>256337.0</td>\n",
       "      <td>25.351605</td>\n",
       "      <td>4.665601</td>\n",
       "      <td>2.000</td>\n",
       "      <td>23.00</td>\n",
       "      <td>25.000</td>\n",
       "      <td>28.000</td>\n",
       "      <td>53.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Bilirubin, Total</th>\n",
       "      <td>56960.0</td>\n",
       "      <td>3.330205</td>\n",
       "      <td>6.660632</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.50</td>\n",
       "      <td>0.900</td>\n",
       "      <td>2.600</td>\n",
       "      <td>82.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calcium, Total</th>\n",
       "      <td>196861.0</td>\n",
       "      <td>8.387876</td>\n",
       "      <td>0.791130</td>\n",
       "      <td>0.000</td>\n",
       "      <td>7.90</td>\n",
       "      <td>8.400</td>\n",
       "      <td>8.800</td>\n",
       "      <td>31.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Calculated Total CO2</th>\n",
       "      <td>223114.0</td>\n",
       "      <td>25.887735</td>\n",
       "      <td>5.354913</td>\n",
       "      <td>0.000</td>\n",
       "      <td>23.00</td>\n",
       "      <td>26.000</td>\n",
       "      <td>29.000</td>\n",
       "      <td>231.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chloride</th>\n",
       "      <td>263521.0</td>\n",
       "      <td>104.013433</td>\n",
       "      <td>6.048956</td>\n",
       "      <td>39.000</td>\n",
       "      <td>100.00</td>\n",
       "      <td>104.000</td>\n",
       "      <td>108.000</td>\n",
       "      <td>155.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Creatinine</th>\n",
       "      <td>265601.0</td>\n",
       "      <td>1.338388</td>\n",
       "      <td>1.356336</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.70</td>\n",
       "      <td>0.900</td>\n",
       "      <td>1.400</td>\n",
       "      <td>73.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Eosinophils</th>\n",
       "      <td>38634.0</td>\n",
       "      <td>1.530233</td>\n",
       "      <td>2.907665</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.800</td>\n",
       "      <td>2.000</td>\n",
       "      <td>97.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Glucose</th>\n",
       "      <td>360949.0</td>\n",
       "      <td>135.896352</td>\n",
       "      <td>72.165565</td>\n",
       "      <td>-251.000</td>\n",
       "      <td>102.00</td>\n",
       "      <td>122.000</td>\n",
       "      <td>150.000</td>\n",
       "      <td>2590.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hematocrit</th>\n",
       "      <td>300095.0</td>\n",
       "      <td>30.888162</td>\n",
       "      <td>5.166604</td>\n",
       "      <td>0.000</td>\n",
       "      <td>27.30</td>\n",
       "      <td>30.300</td>\n",
       "      <td>33.900</td>\n",
       "      <td>77.70</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Hemoglobin</th>\n",
       "      <td>296749.0</td>\n",
       "      <td>10.519762</td>\n",
       "      <td>1.919812</td>\n",
       "      <td>0.000</td>\n",
       "      <td>9.20</td>\n",
       "      <td>10.300</td>\n",
       "      <td>11.600</td>\n",
       "      <td>130.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lactate</th>\n",
       "      <td>79042.0</td>\n",
       "      <td>2.587442</td>\n",
       "      <td>2.480977</td>\n",
       "      <td>0.100</td>\n",
       "      <td>1.20</td>\n",
       "      <td>1.800</td>\n",
       "      <td>2.900</td>\n",
       "      <td>36.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lymphocytes</th>\n",
       "      <td>40359.0</td>\n",
       "      <td>14.231998</td>\n",
       "      <td>13.029516</td>\n",
       "      <td>0.000</td>\n",
       "      <td>6.00</td>\n",
       "      <td>10.900</td>\n",
       "      <td>18.000</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MCH</th>\n",
       "      <td>244709.0</td>\n",
       "      <td>30.286463</td>\n",
       "      <td>2.351838</td>\n",
       "      <td>0.000</td>\n",
       "      <td>29.10</td>\n",
       "      <td>30.400</td>\n",
       "      <td>31.600</td>\n",
       "      <td>48.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MCHC</th>\n",
       "      <td>244844.0</td>\n",
       "      <td>33.825413</td>\n",
       "      <td>1.498184</td>\n",
       "      <td>0.000</td>\n",
       "      <td>32.90</td>\n",
       "      <td>33.900</td>\n",
       "      <td>34.800</td>\n",
       "      <td>40.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MCV</th>\n",
       "      <td>244704.0</td>\n",
       "      <td>89.613149</td>\n",
       "      <td>6.281078</td>\n",
       "      <td>0.000</td>\n",
       "      <td>86.00</td>\n",
       "      <td>89.000</td>\n",
       "      <td>93.000</td>\n",
       "      <td>139.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Magnesium</th>\n",
       "      <td>237650.0</td>\n",
       "      <td>2.049438</td>\n",
       "      <td>0.405154</td>\n",
       "      <td>0.000</td>\n",
       "      <td>1.80</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2.200</td>\n",
       "      <td>37.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Monocytes</th>\n",
       "      <td>40228.0</td>\n",
       "      <td>5.480839</td>\n",
       "      <td>6.361556</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.90</td>\n",
       "      <td>4.300</td>\n",
       "      <td>6.100</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Neutrophils</th>\n",
       "      <td>37915.0</td>\n",
       "      <td>76.377352</td>\n",
       "      <td>16.234455</td>\n",
       "      <td>0.000</td>\n",
       "      <td>70.90</td>\n",
       "      <td>80.100</td>\n",
       "      <td>87.000</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PT</th>\n",
       "      <td>162381.0</td>\n",
       "      <td>16.178182</td>\n",
       "      <td>6.972080</td>\n",
       "      <td>7.000</td>\n",
       "      <td>13.10</td>\n",
       "      <td>14.200</td>\n",
       "      <td>16.500</td>\n",
       "      <td>150.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>PTT</th>\n",
       "      <td>173518.0</td>\n",
       "      <td>43.993670</td>\n",
       "      <td>25.507155</td>\n",
       "      <td>0.150</td>\n",
       "      <td>27.80</td>\n",
       "      <td>33.800</td>\n",
       "      <td>51.800</td>\n",
       "      <td>162.60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Phosphate</th>\n",
       "      <td>198645.0</td>\n",
       "      <td>3.477487</td>\n",
       "      <td>1.286130</td>\n",
       "      <td>0.000</td>\n",
       "      <td>2.70</td>\n",
       "      <td>3.300</td>\n",
       "      <td>4.000</td>\n",
       "      <td>32.80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Platelet Count</th>\n",
       "      <td>254717.0</td>\n",
       "      <td>237.352042</td>\n",
       "      <td>146.191717</td>\n",
       "      <td>4.000</td>\n",
       "      <td>141.00</td>\n",
       "      <td>209.000</td>\n",
       "      <td>297.000</td>\n",
       "      <td>2813.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Potassium</th>\n",
       "      <td>280607.0</td>\n",
       "      <td>4.084886</td>\n",
       "      <td>0.623854</td>\n",
       "      <td>0.800</td>\n",
       "      <td>3.70</td>\n",
       "      <td>4.000</td>\n",
       "      <td>4.400</td>\n",
       "      <td>97.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RDW</th>\n",
       "      <td>244199.0</td>\n",
       "      <td>15.144905</td>\n",
       "      <td>2.148347</td>\n",
       "      <td>0.000</td>\n",
       "      <td>13.60</td>\n",
       "      <td>14.600</td>\n",
       "      <td>16.100</td>\n",
       "      <td>34.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Red Blood Cells</th>\n",
       "      <td>244713.0</td>\n",
       "      <td>3.501585</td>\n",
       "      <td>0.632873</td>\n",
       "      <td>0.000</td>\n",
       "      <td>3.06</td>\n",
       "      <td>3.430</td>\n",
       "      <td>3.880</td>\n",
       "      <td>8.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sodium</th>\n",
       "      <td>267476.0</td>\n",
       "      <td>138.666493</td>\n",
       "      <td>4.998076</td>\n",
       "      <td>74.000</td>\n",
       "      <td>136.00</td>\n",
       "      <td>139.000</td>\n",
       "      <td>141.000</td>\n",
       "      <td>184.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Specific Gravity</th>\n",
       "      <td>35441.0</td>\n",
       "      <td>1.016648</td>\n",
       "      <td>0.009964</td>\n",
       "      <td>0.015</td>\n",
       "      <td>1.01</td>\n",
       "      <td>1.015</td>\n",
       "      <td>1.021</td>\n",
       "      <td>1.08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Urea Nitrogen</th>\n",
       "      <td>264396.0</td>\n",
       "      <td>26.498398</td>\n",
       "      <td>21.558703</td>\n",
       "      <td>0.000</td>\n",
       "      <td>13.00</td>\n",
       "      <td>20.000</td>\n",
       "      <td>32.000</td>\n",
       "      <td>280.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>White Blood Cells</th>\n",
       "      <td>246687.0</td>\n",
       "      <td>11.489575</td>\n",
       "      <td>8.077706</td>\n",
       "      <td>0.100</td>\n",
       "      <td>7.60</td>\n",
       "      <td>10.300</td>\n",
       "      <td>13.800</td>\n",
       "      <td>600.20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pCO2</th>\n",
       "      <td>223095.0</td>\n",
       "      <td>41.802743</td>\n",
       "      <td>10.054990</td>\n",
       "      <td>0.000</td>\n",
       "      <td>36.00</td>\n",
       "      <td>40.000</td>\n",
       "      <td>46.000</td>\n",
       "      <td>247.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pH</th>\n",
       "      <td>280910.0</td>\n",
       "      <td>7.191423</td>\n",
       "      <td>0.616787</td>\n",
       "      <td>0.940</td>\n",
       "      <td>7.31</td>\n",
       "      <td>7.380</td>\n",
       "      <td>7.430</td>\n",
       "      <td>10.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pO2</th>\n",
       "      <td>223106.0</td>\n",
       "      <td>151.722329</td>\n",
       "      <td>102.264953</td>\n",
       "      <td>0.000</td>\n",
       "      <td>85.00</td>\n",
       "      <td>115.000</td>\n",
       "      <td>175.000</td>\n",
       "      <td>797.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    count        mean          std      min  \\\n",
       "LABEL                                                                         \n",
       "Alanine Aminotransferase (ALT)    56767.0  220.221912   818.161372    0.000   \n",
       "Albumin                           37358.0    3.035668     0.695180    1.000   \n",
       "Alkaline Phosphatase              54975.0  140.230341   165.232238    0.000   \n",
       "Anion Gap                        250378.0   13.503702     3.638658   -6.000   \n",
       "Asparate Aminotransferase (AST)   56708.0  243.030472  1050.164745    0.000   \n",
       "Base Excess                      223110.0   -0.015864     4.870077 -413.000   \n",
       "Basophils                         38033.0    0.296372     0.486295    0.000   \n",
       "Bicarbonate                      256337.0   25.351605     4.665601    2.000   \n",
       "Bilirubin, Total                  56960.0    3.330205     6.660632    0.000   \n",
       "Calcium, Total                   196861.0    8.387876     0.791130    0.000   \n",
       "Calculated Total CO2             223114.0   25.887735     5.354913    0.000   \n",
       "Chloride                         263521.0  104.013433     6.048956   39.000   \n",
       "Creatinine                       265601.0    1.338388     1.356336    0.000   \n",
       "Eosinophils                       38634.0    1.530233     2.907665    0.000   \n",
       "Glucose                          360949.0  135.896352    72.165565 -251.000   \n",
       "Hematocrit                       300095.0   30.888162     5.166604    0.000   \n",
       "Hemoglobin                       296749.0   10.519762     1.919812    0.000   \n",
       "Lactate                           79042.0    2.587442     2.480977    0.100   \n",
       "Lymphocytes                       40359.0   14.231998    13.029516    0.000   \n",
       "MCH                              244709.0   30.286463     2.351838    0.000   \n",
       "MCHC                             244844.0   33.825413     1.498184    0.000   \n",
       "MCV                              244704.0   89.613149     6.281078    0.000   \n",
       "Magnesium                        237650.0    2.049438     0.405154    0.000   \n",
       "Monocytes                         40228.0    5.480839     6.361556    0.000   \n",
       "Neutrophils                       37915.0   76.377352    16.234455    0.000   \n",
       "PT                               162381.0   16.178182     6.972080    7.000   \n",
       "PTT                              173518.0   43.993670    25.507155    0.150   \n",
       "Phosphate                        198645.0    3.477487     1.286130    0.000   \n",
       "Platelet Count                   254717.0  237.352042   146.191717    4.000   \n",
       "Potassium                        280607.0    4.084886     0.623854    0.800   \n",
       "RDW                              244199.0   15.144905     2.148347    0.000   \n",
       "Red Blood Cells                  244713.0    3.501585     0.632873    0.000   \n",
       "Sodium                           267476.0  138.666493     4.998076   74.000   \n",
       "Specific Gravity                  35441.0    1.016648     0.009964    0.015   \n",
       "Urea Nitrogen                    264396.0   26.498398    21.558703    0.000   \n",
       "White Blood Cells                246687.0   11.489575     8.077706    0.100   \n",
       "pCO2                             223095.0   41.802743    10.054990    0.000   \n",
       "pH                               280910.0    7.191423     0.616787    0.940   \n",
       "pO2                              223106.0  151.722329   102.264953    0.000   \n",
       "\n",
       "                                    25%      50%      75%       max  \n",
       "LABEL                                                                \n",
       "Alanine Aminotransferase (ALT)    21.00   40.000   97.000  25460.00  \n",
       "Albumin                            2.50    3.000    3.500      6.40  \n",
       "Alkaline Phosphatase              66.00   94.000  151.000   4695.00  \n",
       "Anion Gap                         11.00   13.000   15.000     67.00  \n",
       "Asparate Aminotransferase (AST)   26.00   48.000  106.000  36400.00  \n",
       "Base Excess                       -2.00    0.000    2.000    162.00  \n",
       "Basophils                          0.00    0.200    0.400     40.00  \n",
       "Bicarbonate                       23.00   25.000   28.000     53.00  \n",
       "Bilirubin, Total                   0.50    0.900    2.600     82.80  \n",
       "Calcium, Total                     7.90    8.400    8.800     31.20  \n",
       "Calculated Total CO2              23.00   26.000   29.000    231.00  \n",
       "Chloride                         100.00  104.000  108.000    155.00  \n",
       "Creatinine                         0.70    0.900    1.400     73.00  \n",
       "Eosinophils                        0.10    0.800    2.000     97.00  \n",
       "Glucose                          102.00  122.000  150.000   2590.00  \n",
       "Hematocrit                        27.30   30.300   33.900     77.70  \n",
       "Hemoglobin                         9.20   10.300   11.600    130.00  \n",
       "Lactate                            1.20    1.800    2.900     36.00  \n",
       "Lymphocytes                        6.00   10.900   18.000    100.00  \n",
       "MCH                               29.10   30.400   31.600     48.00  \n",
       "MCHC                              32.90   33.900   34.800     40.00  \n",
       "MCV                               86.00   89.000   93.000    139.00  \n",
       "Magnesium                          1.80    2.000    2.200     37.50  \n",
       "Monocytes                          2.90    4.300    6.100    100.00  \n",
       "Neutrophils                       70.90   80.100   87.000    100.00  \n",
       "PT                                13.10   14.200   16.500    150.00  \n",
       "PTT                               27.80   33.800   51.800    162.60  \n",
       "Phosphate                          2.70    3.300    4.000     32.80  \n",
       "Platelet Count                   141.00  209.000  297.000   2813.00  \n",
       "Potassium                          3.70    4.000    4.400     97.00  \n",
       "RDW                               13.60   14.600   16.100     34.40  \n",
       "Red Blood Cells                    3.06    3.430    3.880      8.44  \n",
       "Sodium                           136.00  139.000  141.000    184.00  \n",
       "Specific Gravity                   1.01    1.015    1.021      1.08  \n",
       "Urea Nitrogen                     13.00   20.000   32.000    280.00  \n",
       "White Blood Cells                  7.60   10.300   13.800    600.20  \n",
       "pCO2                              36.00   40.000   46.000    247.00  \n",
       "pH                                 7.31    7.380    7.430     10.00  \n",
       "pO2                               85.00  115.000  175.000    797.00  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lab3.groupby(\"LABEL\")[\"VALUENUM\"].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Glucose : mettre -1 aux résultats négatifs et supprimer les autres entrées dont la valeur numérique est NaN.\n",
    "lab3.loc[(lab3[\"LABEL\"]==\"Glucose\")&(lab3[\"VALUENUM\"].isnull())&(lab3[\"VALUE\"]==\"NEG\"),\"VALUENUM\"]=-1\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Glucose\")&(lab3[\"VALUENUM\"].isnull())].index).copy()\n",
    "\n",
    "#Retirer les entrées avec NaN aux values et valuenum\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"VALUENUM\"].isnull())&(lab3[\"VALUE\"].isnull())].index).copy()\n",
    "\n",
    "#Remove the remaining NAN Values\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"VALUENUM\"].isnull())].index).copy()\n",
    "\n",
    "#Remove anion gaps lower than 0\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"VALUENUM\"]<0)&(lab3[\"LABEL\"]==\"Anion Gap\")].index).copy()\n",
    "\n",
    "#Remove BE <-50\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Base Excess\")&(lab3[\"VALUENUM\"]<-50)].index).copy()\n",
    "#Remove BE >50\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Base Excess\")&(lab3[\"VALUENUM\"]>50)].index).copy()\n",
    "\n",
    "#Remove high Hemoglobins\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Hemoglobin\")&(lab3[\"VALUENUM\"]>25)].index).copy()\n",
    "\n",
    "#Clean some glucose entries\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Glucose\")&(lab3[\"VALUENUM\"]>2000)&(lab3[\"HADM_ID\"]==103500.0)].index).copy()\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Glucose\")&(lab3[\"VALUENUM\"]>2000)&(lab3[\"HADM_ID\"]==117066.0)].index).copy()\n",
    "\n",
    "#Clean toO high levels of Potassium\n",
    "lab3=lab3.drop(lab3.loc[(lab3[\"LABEL\"]==\"Potassium\")&(lab3[\"VALUENUM\"]>30)].index).copy()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "lab3.to_csv(file_path+\"LAB_processed.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge the admission time.\n",
    "adm_short=adm[[\"HADM_ID\",\"ADMITTIME\",\"ELAPSED_TIME\",\"DEATHTAG\"]]\n",
    "lab4=pd.merge(lab3,adm_short,on=\"HADM_ID\")\n",
    "lab4['CHARTTIME']=pd.to_datetime(lab4[\"CHARTTIME\"], format='%Y-%m-%d %H:%M:%S')\n",
    "#lab4['ADMITTIME']=pd.to_datetime(lab4[\"ADMITTIME\"], format='%Y-%m-%d %H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Set the reference time as the admission time for each admission.\n",
    "ref_time=lab4.groupby(\"HADM_ID\")[\"CHARTTIME\"].min()\n",
    "#ref_time=lab4.groupby(\"HADM_ID\")[\"ADMITTIME\"].min()\n",
    "lab5=pd.merge(ref_time.to_frame(name=\"REF_TIME\"),lab4,left_index=True,right_on=\"HADM_ID\")\n",
    "lab5[\"TIME_STAMP\"]=lab5[\"CHARTTIME\"]-lab5[\"REF_TIME\"]\n",
    "assert(len(lab5.loc[lab5[\"TIME_STAMP\"]<timedelta(hours=0)].index)==0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a label code (int) for the labels.\n",
    "label_dict=dict(zip(list(lab5[\"LABEL\"].unique()),range(len(list(lab5[\"LABEL\"].unique())))))\n",
    "lab5[\"LABEL_CODE\"]=lab5[\"LABEL\"].map(label_dict)\n",
    "lab_short=lab5[[\"SUBJECT_ID\",\"HADM_ID\",\"VALUENUM\",\"TIME_STAMP\",\"LABEL_CODE\",\"DEATHTAG\"]]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Time binning of the data\n",
    "First we select the data up to a certain time limit (48 hours)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of patients considered :23418\n"
     ]
    }
   ],
   "source": [
    "#Now only select values within 48 hours.\n",
    "lab_short=lab_short.loc[(lab_short[\"TIME_STAMP\"]<timedelta(hours=48))]\n",
    "print(\"Number of patients considered :\"+str(lab_short[\"SUBJECT_ID\"].nunique()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-16-4700ca94a294>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mbin_k\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mbins_num\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m     \u001b[0mtime_stamp_str\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"TIME_STAMP_Bin_\"\u001b[0m\u001b[0;34m+\u001b[0m\u001b[0mstr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mbin_k\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 7\u001b[0;31m     \u001b[0mlab_short_binned\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mtime_stamp_str\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mround\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlab_short_binned\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"TIME_STAMP\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdt\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtotal_seconds\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mbin_k\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m100\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0;36m36\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mint\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      8\u001b[0m     \u001b[0mhits_prop\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlab_short_binned\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mduplicated\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msubset\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"HADM_ID\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"LABEL_CODE\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mtime_stamp_str\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msum\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m/\u001b[0m\u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlab_short_binned\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      9\u001b[0m     \u001b[0mhits_vec\u001b[0m\u001b[0;34m+=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mhits_prop\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/accessor.py\u001b[0m in \u001b[0;36mf\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    113\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    114\u001b[0m             \u001b[0;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 115\u001b[0;31m                 \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_delegate_method\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    116\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    117\u001b[0m             \u001b[0mf\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__name__\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/indexes/accessors.py\u001b[0m in \u001b[0;36m_delegate_method\u001b[0;34m(self, name, *args, **kwargs)\u001b[0m\n\u001b[1;32m    129\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    130\u001b[0m         \u001b[0mmethod\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 131\u001b[0;31m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmethod\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    132\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    133\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mis_list_like\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresult\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/indexes/timedeltas.py\u001b[0m in \u001b[0;36mtotal_seconds\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    450\u001b[0m         \u001b[0;34m.\u001b[0m\u001b[0;34m.\u001b[0m \u001b[0mversionadded\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;36m0.17\u001b[0m\u001b[0;36m.0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    451\u001b[0m         \"\"\"\n\u001b[0;32m--> 452\u001b[0;31m         return Index(self._maybe_mask_results(1e-9 * self.asi8),\n\u001b[0m\u001b[1;32m    453\u001b[0m                      name=self.name)\n\u001b[1;32m    454\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/indexes/datetimelike.py\u001b[0m in \u001b[0;36m_maybe_mask_results\u001b[0;34m(self, result, fill_value, convert)\u001b[0m\n\u001b[1;32m    456\u001b[0m         \"\"\"\n\u001b[1;32m    457\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 458\u001b[0;31m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhasnans\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    459\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mconvert\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    460\u001b[0m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconvert\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/properties.pyx\u001b[0m in \u001b[0;36mpandas._libs.properties.cache_readonly.__get__\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/indexes/base.py\u001b[0m in \u001b[0;36mhasnans\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1879\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0marray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mint64\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1880\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1881\u001b[0;31m     \u001b[0;34m@\u001b[0m\u001b[0mcache_readonly\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1882\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mhasnans\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1883\u001b[0m         \u001b[0;34m\"\"\" return if I have any nans; enables various perf speedups \"\"\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "#Plot the number of \"hits\" based on the binning. That is, the number of measurements falling into the same bin in function of the number of bins\n",
    "bins_num=range(1,10)\n",
    "lab_short_binned=lab_short.copy()\n",
    "hits_vec=[]\n",
    "for bin_k in bins_num:\n",
    "    time_stamp_str=\"TIME_STAMP_Bin_\"+str(bin_k)\n",
    "    lab_short_binned[time_stamp_str]=round(lab_short_binned[\"TIME_STAMP\"].dt.total_seconds()*bin_k/(100*36)).astype(int)\n",
    "    hits_prop=lab_short_binned.duplicated(subset=[\"HADM_ID\",\"LABEL_CODE\",time_stamp_str]).sum()/len(lab_short_binned.index)\n",
    "    hits_vec+=[hits_prop]\n",
    "plt.plot(bins_num,hits_vec)\n",
    "plt.title(\"Percentage of hits in function of the binning factor\")\n",
    "plt.xlabel(\"Number of bins/hour\")\n",
    "plt.ylabel(\"% of hits\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We then choose a binning factor of 2. \n",
    "#Set the time as an integer. We take 2 bins per hour\n",
    "lab_short[\"TIME_STAMP\"]=round(lab_short[\"TIME_STAMP\"].dt.total_seconds()*2/(100*36)).astype(int)\n",
    "#Then sort the dataframe with order : Admission ID, Label Code and time stamps\n",
    "lab_short=lab_short.sort_values(by=[\"HADM_ID\",\"LABEL_CODE\",\"TIME_STAMP\"],ascending=[1,1,1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "lab_short.duplicated(subset=[\"HADM_ID\",\"LABEL_CODE\",\"TIME_STAMP\"]).value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Then save locally\n",
    "lab_short.to_csv(file_path+\"lab_events_short.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "len(lab_short.index)/(22000*39*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "#Example of time series for an ICU stay.\n",
    "plt.plot(lab_short.loc[(lab_short[\"HADM_ID\"]==130421)&(lab_short[\"LABEL_CODE\"]==25),\"TIME_STAMP\"],lab_short.loc[(lab_short[\"HADM_ID\"]==130421)&(lab_short[\"LABEL_CODE\"]==25),\"VALUENUM\"])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create an extra dataset with the deathtags of each hadm_id.\n",
    "death_tags_s=lab_short.groupby(\"HADM_ID\")[\"DEATHTAG\"].unique().astype(int)\n",
    "death_tags_df=pd.DataFrame({\"HADM_ID\":death_tags_s.index,\"DEATHTAG\":death_tags_s.values})\n",
    "death_tags_df.to_csv(file_path+\"death_tags.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataset creation for LSTM operation (One sample = one patient history)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyboardInterrupt",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m                         Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-19-5d5a05922e75>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0munique_hadms\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlab_short\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"HADM_ID\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0munique\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mlab_code\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mrange\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;36m30\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m     \u001b[0mlab_short\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlab_short\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop_duplicates\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"LABEL_CODE\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"TIME_STAMP\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\"HADM_ID\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      7\u001b[0m     \u001b[0mlab_code_view\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlab_short\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mlab_short\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"LABEL_CODE\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m==\u001b[0m\u001b[0mlab_code\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m     \u001b[0mpivoted\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mlab_code_view\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpivot\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"HADM_ID\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"TIME_STAMP\"\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m\"VALUENUM\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreset_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mdrop_duplicates\u001b[0;34m(self, subset, keep, inplace)\u001b[0m\n\u001b[1;32m   3533\u001b[0m         \"\"\"\n\u001b[1;32m   3534\u001b[0m         \u001b[0minplace\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mvalidate_bool_kwarg\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minplace\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'inplace'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 3535\u001b[0;31m         \u001b[0mduplicated\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mduplicated\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msubset\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mkeep\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mkeep\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3536\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3537\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0minplace\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mduplicated\u001b[0;34m(self, subset, keep)\u001b[0m\n\u001b[1;32m   3580\u001b[0m         vals = (col.values for name, col in self.iteritems()\n\u001b[1;32m   3581\u001b[0m                 if name in subset)\n\u001b[0;32m-> 3582\u001b[0;31m         \u001b[0mlabels\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mshape\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlist\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mzip\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mmap\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvals\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   3583\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3584\u001b[0m         \u001b[0mids\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mget_group_index\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlabels\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mshape\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0msort\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mxnull\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36mf\u001b[0;34m(vals)\u001b[0m\n\u001b[1;32m   3568\u001b[0m         \u001b[0;32mdef\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvals\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3569\u001b[0m             labels, shape = algorithms.factorize(\n\u001b[0;32m-> 3570\u001b[0;31m                 vals, size_hint=min(len(self), _SIZE_HINT_LIMIT))\n\u001b[0m\u001b[1;32m   3571\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mlabels\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'i8'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   3572\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/algorithms.py\u001b[0m in \u001b[0;36mfactorize\u001b[0;34m(values, sort, order, na_sentinel, size_hint)\u001b[0m\n\u001b[1;32m    469\u001b[0m     \u001b[0muniques\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mvec_klass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    470\u001b[0m     \u001b[0mcheck_nulls\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mis_integer_dtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0moriginal\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 471\u001b[0;31m     \u001b[0mlabels\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget_labels\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muniques\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mna_sentinel\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcheck_nulls\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    472\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    473\u001b[0m     \u001b[0mlabels\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_ensure_platform_int\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mlabels\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/hashtable_class_helper.pxi\u001b[0m in \u001b[0;36mpandas._libs.hashtable.Int64HashTable.get_labels\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m~/miniconda3/envs/pytorch/lib/python3.6/site-packages/numpy/core/numeric.py\u001b[0m in \u001b[0;36masarray\u001b[0;34m(a, dtype, order)\u001b[0m\n\u001b[1;32m    422\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    423\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 424\u001b[0;31m \u001b[0;32mdef\u001b[0m \u001b[0masarray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0morder\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    425\u001b[0m     \"\"\"Convert the input to an array.\n\u001b[1;32m    426\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyboardInterrupt\u001b[0m: "
     ]
    }
   ],
   "source": [
    "#Transform the dataframe as one row per patient/feature (for LSTM operation)\n",
    "import numpy as np\n",
    "results=[]\n",
    "unique_hadms=lab_short[\"HADM_ID\"].unique()\n",
    "for lab_code in range(30):\n",
    "    lab_short=lab_short.drop_duplicates([\"LABEL_CODE\",\"TIME_STAMP\",\"HADM_ID\"])\n",
    "    lab_code_view=lab_short.loc[lab_short[\"LABEL_CODE\"]==lab_code]\n",
    "    pivoted=lab_code_view.pivot(index=\"HADM_ID\",columns=\"TIME_STAMP\",values=\"VALUENUM\").reset_index()\n",
    "    pivoted[\"LABEL_CODE\"]=lab_code\n",
    "    \n",
    "    non_code=lab_short.loc[lab_short[\"LABEL_CODE\"]==lab_code,\"HADM_ID\"].unique()\n",
    "    missing_hadms=np.setdiff1d(unique_hadms,non_code)\n",
    "    \n",
    "    df_missing_hadms=pd.DataFrame(data=np.vstack((missing_hadms,lab_code*np.ones(missing_hadms.shape))).T,columns=[\"HADM_ID\",\"LABEL_CODE\"])\n",
    "    pivoted_full=pd.concat([pivoted,df_missing_hadms])\n",
    "    \n",
    "    results.append(pd.merge(pivoted_full,death_tags_df,on=\"HADM_ID\"))\n",
    "df_pre_proc=pd.concat(results)\n",
    "\n",
    "#We create a new HADM_ID which goes from 0 to nunique()\n",
    "d=dict(zip(df_pre_proc[\"HADM_ID\"].unique(),range(df_pre_proc[\"HADM_ID\"].nunique())))\n",
    "df_pre_proc[\"UNIQUE_ID\"]=df_pre_proc[\"HADM_ID\"].map(d)\n",
    "\n",
    "df_pre_proc.to_csv(file_path+\"lab_short_pre_proc.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/XXXX/miniconda3/envs/pytorch/lib/python3.6/site-packages/pandas/core/generic.py:4619: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  self._update_inplace(new_data)\n"
     ]
    }
   ],
   "source": [
    "#Divide the LSTM dataset in train/val/test sets and save them locally.\n",
    "val_num=int(0.2*df_pre_proc[\"UNIQUE_ID\"].nunique())\n",
    "test_num=int(0.1*df_pre_proc[\"UNIQUE_ID\"].nunique())\n",
    "\n",
    "patients_list=df_pre_proc[\"UNIQUE_ID\"].unique()\n",
    "validation_choice=np.random.choice(patients_list,size=val_num,replace=False)\n",
    "patients_list=np.setdiff1d(patients_list,validation_choice)\n",
    "test_choice=np.random.choice(patients_list,size=test_num,replace=False)\n",
    "patients_list=np.setdiff1d(patients_list,test_choice)\n",
    "\n",
    "df_val=df_pre_proc.loc[df_pre_proc[\"UNIQUE_ID\"].isin(validation_choice)]\n",
    "d=dict(zip(df_val[\"UNIQUE_ID\"].unique(),range(df_val[\"UNIQUE_ID\"].nunique())))\n",
    "df_val[\"UNIQUE_ID\"].replace(d,inplace=True)\n",
    "\n",
    "df_test=df_pre_proc.loc[df_pre_proc[\"UNIQUE_ID\"].isin(test_choice)]\n",
    "d=dict(zip(df_test[\"UNIQUE_ID\"].unique(),range(df_test[\"UNIQUE_ID\"].nunique())))\n",
    "df_test[\"UNIQUE_ID\"].replace(d,inplace=True)\n",
    "\n",
    "df_train=df_pre_proc.loc[df_pre_proc[\"UNIQUE_ID\"].isin(patients_list)]\n",
    "d=dict(zip(df_train[\"UNIQUE_ID\"].unique(),range(df_train[\"UNIQUE_ID\"].nunique())))\n",
    "df_train[\"UNIQUE_ID\"].replace(d,inplace=True)\n",
    "\n",
    "assert((len(df_val.index)+len(df_test.index)+len(df_train.index))==len(df_pre_proc.index))\n",
    "\n",
    "df_val.to_csv(file_path+\"lab_short_pre_proc_val.csv\")\n",
    "df_test.to_csv(file_path+\"lab_short_pre_proc_test.csv\")\n",
    "df_train.to_csv(file_path+\"lab_short_pre_proc_train.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Dataframe creation for Tensor Factorization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "#We copy the lab_short with the required columns\n",
    "lab_short_tensor=lab_short[[\"HADM_ID\",\"VALUENUM\",\"TIME_STAMP\",\"LABEL_CODE\",\"DEATHTAG\"]].copy()\n",
    "#Creation of a unique index\n",
    "d=dict(zip(lab_short_tensor[\"HADM_ID\"].unique(),range(lab_short_tensor[\"HADM_ID\"].nunique())))\n",
    "lab_short_tensor[\"UNIQUE_ID\"]=lab_short_tensor[\"HADM_ID\"].map(d)\n",
    "#Apply the same unique index for the death tags.\n",
    "death_tags_df[\"UNIQUE_ID\"]=death_tags_df[\"HADM_ID\"].map(d)\n",
    "death_tags_df.to_csv(file_path+\"death_tag_tensor.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ROW_ID</th>\n",
       "      <th>SUBJECT_ID</th>\n",
       "      <th>HADM_ID</th>\n",
       "      <th>SEQ_NUM</th>\n",
       "      <th>ICD9_CODE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1297</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1298</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>2.0</td>\n",
       "      <td>486</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1299</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>3.0</td>\n",
       "      <td>58281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1300</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5855</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1301</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1302</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1303</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1304</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>8.0</td>\n",
       "      <td>2767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1305</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>9.0</td>\n",
       "      <td>7243</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1306</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>10.0</td>\n",
       "      <td>45829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1307</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>11.0</td>\n",
       "      <td>2875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1308</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>12.0</td>\n",
       "      <td>28521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1309</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>13.0</td>\n",
       "      <td>28529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1310</td>\n",
       "      <td>109</td>\n",
       "      <td>172335</td>\n",
       "      <td>14.0</td>\n",
       "      <td>27541</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>1311</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>1.0</td>\n",
       "      <td>40301</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>1312</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>1313</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>3.0</td>\n",
       "      <td>58381</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>1314</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>1315</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1316</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>1317</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>7.0</td>\n",
       "      <td>28521</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>1318</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>8.0</td>\n",
       "      <td>45829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1319</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>9.0</td>\n",
       "      <td>32723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>1320</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>10.0</td>\n",
       "      <td>22804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>1321</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>11.0</td>\n",
       "      <td>33829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>1322</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>12.0</td>\n",
       "      <td>78900</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>1323</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>13.0</td>\n",
       "      <td>79092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>1324</td>\n",
       "      <td>109</td>\n",
       "      <td>173633</td>\n",
       "      <td>14.0</td>\n",
       "      <td>V4511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>1488</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>1.0</td>\n",
       "      <td>53100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>1489</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>2.0</td>\n",
       "      <td>41071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>1490</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>1491</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>4.0</td>\n",
       "      <td>41401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>1492</td>\n",
       "      <td>112</td>\n",
       "      <td>174105</td>\n",
       "      <td>5.0</td>\n",
       "      <td>725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>1493</td>\n",
       "      <td>113</td>\n",
       "      <td>109976</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>1494</td>\n",
       "      <td>113</td>\n",
       "      <td>109976</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>1495</td>\n",
       "      <td>113</td>\n",
       "      <td>109976</td>\n",
       "      <td>3.0</td>\n",
       "      <td>53081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>1496</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>1.0</td>\n",
       "      <td>41401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>1497</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>1498</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>3.0</td>\n",
       "      <td>48283</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>1499</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>1500</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1501</td>\n",
       "      <td>114</td>\n",
       "      <td>178393</td>\n",
       "      <td>6.0</td>\n",
       "      <td>3051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>1502</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>1503</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>1504</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>1505</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4240</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>1506</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>1507</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>6.0</td>\n",
       "      <td>99859</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>1508</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>7.0</td>\n",
       "      <td>6822</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>1509</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>1510</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>9.0</td>\n",
       "      <td>5990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>1511</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>10.0</td>\n",
       "      <td>4280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>1512</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>11.0</td>\n",
       "      <td>34982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>1513</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>12.0</td>\n",
       "      <td>4019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>1514</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>13.0</td>\n",
       "      <td>V1000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>1515</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>14.0</td>\n",
       "      <td>V453</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>1516</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>15.0</td>\n",
       "      <td>V5865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>1517</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>1518</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>17.0</td>\n",
       "      <td>2518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>1519</td>\n",
       "      <td>115</td>\n",
       "      <td>114585</td>\n",
       "      <td>18.0</td>\n",
       "      <td>E9320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>1520</td>\n",
       "      <td>116</td>\n",
       "      <td>127203</td>\n",
       "      <td>1.0</td>\n",
       "      <td>V3001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>1521</td>\n",
       "      <td>116</td>\n",
       "      <td>127203</td>\n",
       "      <td>2.0</td>\n",
       "      <td>V053</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>1522</td>\n",
       "      <td>116</td>\n",
       "      <td>127203</td>\n",
       "      <td>3.0</td>\n",
       "      <td>V290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>1523</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>1524</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7895</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>1525</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>3.0</td>\n",
       "      <td>07054</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>1526</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>1527</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>5.0</td>\n",
       "      <td>4280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68</th>\n",
       "      <td>1528</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>6.0</td>\n",
       "      <td>2851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69</th>\n",
       "      <td>1529</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>1530</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>8.0</td>\n",
       "      <td>4019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>1531</td>\n",
       "      <td>117</td>\n",
       "      <td>140784</td>\n",
       "      <td>9.0</td>\n",
       "      <td>25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>1532</td>\n",
       "      <td>117</td>\n",
       "      <td>164853</td>\n",
       "      <td>1.0</td>\n",
       "      <td>570</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>1533</td>\n",
       "      <td>117</td>\n",
       "      <td>164853</td>\n",
       "      <td>2.0</td>\n",
       "      <td>07044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>1534</td>\n",
       "      <td>117</td>\n",
       "      <td>164853</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650972</th>\n",
       "      <td>638480</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>4.0</td>\n",
       "      <td>5854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650973</th>\n",
       "      <td>638481</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>5.0</td>\n",
       "      <td>42833</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650974</th>\n",
       "      <td>638482</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>6.0</td>\n",
       "      <td>72888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650975</th>\n",
       "      <td>638483</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650976</th>\n",
       "      <td>638484</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>8.0</td>\n",
       "      <td>40390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650977</th>\n",
       "      <td>638485</td>\n",
       "      <td>97172</td>\n",
       "      <td>133092</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650978</th>\n",
       "      <td>639734</td>\n",
       "      <td>97476</td>\n",
       "      <td>189690</td>\n",
       "      <td>3.0</td>\n",
       "      <td>V4976</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650979</th>\n",
       "      <td>639735</td>\n",
       "      <td>97476</td>\n",
       "      <td>189690</td>\n",
       "      <td>4.0</td>\n",
       "      <td>56210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650980</th>\n",
       "      <td>639736</td>\n",
       "      <td>97476</td>\n",
       "      <td>189690</td>\n",
       "      <td>5.0</td>\n",
       "      <td>30391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650981</th>\n",
       "      <td>639737</td>\n",
       "      <td>97476</td>\n",
       "      <td>189690</td>\n",
       "      <td>6.0</td>\n",
       "      <td>33829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650982</th>\n",
       "      <td>639738</td>\n",
       "      <td>97476</td>\n",
       "      <td>189690</td>\n",
       "      <td>7.0</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650983</th>\n",
       "      <td>639739</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650984</th>\n",
       "      <td>639740</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650985</th>\n",
       "      <td>639741</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650986</th>\n",
       "      <td>639742</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650987</th>\n",
       "      <td>639743</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2720</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650988</th>\n",
       "      <td>639744</td>\n",
       "      <td>97484</td>\n",
       "      <td>172304</td>\n",
       "      <td>6.0</td>\n",
       "      <td>53081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650989</th>\n",
       "      <td>639745</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>1.0</td>\n",
       "      <td>566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650990</th>\n",
       "      <td>639746</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>2.0</td>\n",
       "      <td>25062</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650991</th>\n",
       "      <td>639747</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650992</th>\n",
       "      <td>639748</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V5867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650993</th>\n",
       "      <td>639749</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>5.0</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650994</th>\n",
       "      <td>639750</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650995</th>\n",
       "      <td>639751</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650996</th>\n",
       "      <td>639752</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>8.0</td>\n",
       "      <td>53081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650997</th>\n",
       "      <td>639753</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>9.0</td>\n",
       "      <td>3051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650998</th>\n",
       "      <td>639754</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>10.0</td>\n",
       "      <td>27800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>650999</th>\n",
       "      <td>639755</td>\n",
       "      <td>97488</td>\n",
       "      <td>152542</td>\n",
       "      <td>11.0</td>\n",
       "      <td>78820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651000</th>\n",
       "      <td>639756</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>1.0</td>\n",
       "      <td>43411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651001</th>\n",
       "      <td>639757</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3485</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651002</th>\n",
       "      <td>639758</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3484</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651003</th>\n",
       "      <td>639759</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>4.0</td>\n",
       "      <td>430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651004</th>\n",
       "      <td>639760</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>5.0</td>\n",
       "      <td>34830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651005</th>\n",
       "      <td>639761</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>6.0</td>\n",
       "      <td>99731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651006</th>\n",
       "      <td>639762</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>7.0</td>\n",
       "      <td>51883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651007</th>\n",
       "      <td>639763</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>8.0</td>\n",
       "      <td>5990</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651008</th>\n",
       "      <td>639764</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>9.0</td>\n",
       "      <td>34291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651009</th>\n",
       "      <td>639765</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>10.0</td>\n",
       "      <td>29181</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651010</th>\n",
       "      <td>639766</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>11.0</td>\n",
       "      <td>V4588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651011</th>\n",
       "      <td>639767</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>12.0</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651012</th>\n",
       "      <td>639768</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>13.0</td>\n",
       "      <td>4019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651013</th>\n",
       "      <td>639769</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651014</th>\n",
       "      <td>639770</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>15.0</td>\n",
       "      <td>25000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651015</th>\n",
       "      <td>639771</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>16.0</td>\n",
       "      <td>V5867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651016</th>\n",
       "      <td>639772</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>17.0</td>\n",
       "      <td>4280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651017</th>\n",
       "      <td>639773</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>18.0</td>\n",
       "      <td>3051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651018</th>\n",
       "      <td>639774</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>19.0</td>\n",
       "      <td>7843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651019</th>\n",
       "      <td>639775</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0414</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651020</th>\n",
       "      <td>639776</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>21.0</td>\n",
       "      <td>30391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651021</th>\n",
       "      <td>639777</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>22.0</td>\n",
       "      <td>E8798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651022</th>\n",
       "      <td>639778</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>23.0</td>\n",
       "      <td>78791</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651023</th>\n",
       "      <td>639779</td>\n",
       "      <td>97488</td>\n",
       "      <td>161999</td>\n",
       "      <td>24.0</td>\n",
       "      <td>V4986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651024</th>\n",
       "      <td>639780</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>1.0</td>\n",
       "      <td>34680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651025</th>\n",
       "      <td>639781</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>2.0</td>\n",
       "      <td>7843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651026</th>\n",
       "      <td>639782</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>3.0</td>\n",
       "      <td>7455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651027</th>\n",
       "      <td>639783</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>4.0</td>\n",
       "      <td>78194</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651028</th>\n",
       "      <td>639784</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>5.0</td>\n",
       "      <td>36840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651029</th>\n",
       "      <td>639785</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>6.0</td>\n",
       "      <td>7813</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651030</th>\n",
       "      <td>639786</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>7.0</td>\n",
       "      <td>7820</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651031</th>\n",
       "      <td>639787</td>\n",
       "      <td>97492</td>\n",
       "      <td>189314</td>\n",
       "      <td>8.0</td>\n",
       "      <td>311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651032</th>\n",
       "      <td>639788</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651033</th>\n",
       "      <td>639789</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651034</th>\n",
       "      <td>639790</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>3.0</td>\n",
       "      <td>20192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651035</th>\n",
       "      <td>639791</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>4.0</td>\n",
       "      <td>135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651036</th>\n",
       "      <td>639792</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>5.0</td>\n",
       "      <td>27669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651037</th>\n",
       "      <td>639793</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651038</th>\n",
       "      <td>639794</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>7.0</td>\n",
       "      <td>42731</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651039</th>\n",
       "      <td>639795</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>8.0</td>\n",
       "      <td>V5861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651040</th>\n",
       "      <td>639796</td>\n",
       "      <td>97497</td>\n",
       "      <td>168949</td>\n",
       "      <td>9.0</td>\n",
       "      <td>45829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651041</th>\n",
       "      <td>639797</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651042</th>\n",
       "      <td>639798</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>2.0</td>\n",
       "      <td>20280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651043</th>\n",
       "      <td>639799</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>3.0</td>\n",
       "      <td>V5869</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651044</th>\n",
       "      <td>639800</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>4.0</td>\n",
       "      <td>V1279</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651045</th>\n",
       "      <td>639801</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>5.0</td>\n",
       "      <td>5275</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>651046</th>\n",
       "      <td>639802</td>\n",
       "      <td>97503</td>\n",
       "      <td>188195</td>\n",
       "      <td>6.0</td>\n",
       "      <td>5569</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>651047 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ROW_ID  SUBJECT_ID  HADM_ID  SEQ_NUM ICD9_CODE\n",
       "0         1297         109   172335      1.0     40301\n",
       "1         1298         109   172335      2.0       486\n",
       "2         1299         109   172335      3.0     58281\n",
       "3         1300         109   172335      4.0      5855\n",
       "4         1301         109   172335      5.0      4254\n",
       "5         1302         109   172335      6.0      2762\n",
       "6         1303         109   172335      7.0      7100\n",
       "7         1304         109   172335      8.0      2767\n",
       "8         1305         109   172335      9.0      7243\n",
       "9         1306         109   172335     10.0     45829\n",
       "10        1307         109   172335     11.0      2875\n",
       "11        1308         109   172335     12.0     28521\n",
       "12        1309         109   172335     13.0     28529\n",
       "13        1310         109   172335     14.0     27541\n",
       "14        1311         109   173633      1.0     40301\n",
       "15        1312         109   173633      2.0      5856\n",
       "16        1313         109   173633      3.0     58381\n",
       "17        1314         109   173633      4.0      7100\n",
       "18        1315         109   173633      5.0      5589\n",
       "19        1316         109   173633      6.0      2875\n",
       "20        1317         109   173633      7.0     28521\n",
       "21        1318         109   173633      8.0     45829\n",
       "22        1319         109   173633      9.0     32723\n",
       "23        1320         109   173633     10.0     22804\n",
       "24        1321         109   173633     11.0     33829\n",
       "25        1322         109   173633     12.0     78900\n",
       "26        1323         109   173633     13.0     79092\n",
       "27        1324         109   173633     14.0     V4511\n",
       "28        1488         112   174105      1.0     53100\n",
       "29        1489         112   174105      2.0     41071\n",
       "30        1490         112   174105      3.0      2859\n",
       "31        1491         112   174105      4.0     41401\n",
       "32        1492         112   174105      5.0       725\n",
       "33        1493         113   109976      1.0      1915\n",
       "34        1494         113   109976      2.0      3314\n",
       "35        1495         113   109976      3.0     53081\n",
       "36        1496         114   178393      1.0     41401\n",
       "37        1497         114   178393      2.0      4111\n",
       "38        1498         114   178393      3.0     48283\n",
       "39        1499         114   178393      4.0      2859\n",
       "40        1500         114   178393      5.0      2720\n",
       "41        1501         114   178393      6.0      3051\n",
       "42        1502         115   114585      1.0      1940\n",
       "43        1503         115   114585      2.0      1977\n",
       "44        1504         115   114585      3.0      2553\n",
       "45        1505         115   114585      4.0      4240\n",
       "46        1506         115   114585      5.0      5845\n",
       "47        1507         115   114585      6.0     99859\n",
       "48        1508         115   114585      7.0      6822\n",
       "49        1509         115   114585      8.0      5119\n",
       "50        1510         115   114585      9.0      5990\n",
       "51        1511         115   114585     10.0      4280\n",
       "52        1512         115   114585     11.0     34982\n",
       "53        1513         115   114585     12.0      4019\n",
       "54        1514         115   114585     13.0     V1000\n",
       "55        1515         115   114585     14.0      V453\n",
       "56        1516         115   114585     15.0     V5865\n",
       "57        1517         115   114585     16.0      0413\n",
       "58        1518         115   114585     17.0      2518\n",
       "59        1519         115   114585     18.0     E9320\n",
       "60        1520         116   127203      1.0     V3001\n",
       "61        1521         116   127203      2.0      V053\n",
       "62        1522         116   127203      3.0      V290\n",
       "63        1523         117   140784      1.0      5715\n",
       "64        1524         117   140784      2.0      7895\n",
       "65        1525         117   140784      3.0     07054\n",
       "66        1526         117   140784      4.0      2875\n",
       "67        1527         117   140784      5.0      4280\n",
       "68        1528         117   140784      6.0      2851\n",
       "69        1529         117   140784      7.0      2765\n",
       "70        1530         117   140784      8.0      4019\n",
       "71        1531         117   140784      9.0     25000\n",
       "72        1532         117   164853      1.0       570\n",
       "73        1533         117   164853      2.0     07044\n",
       "74        1534         117   164853      3.0      5712\n",
       "...        ...         ...      ...      ...       ...\n",
       "650972  638480       97172   133092      4.0      5854\n",
       "650973  638481       97172   133092      5.0     42833\n",
       "650974  638482       97172   133092      6.0     72888\n",
       "650975  638483       97172   133092      7.0      2762\n",
       "650976  638484       97172   133092      8.0     40390\n",
       "650977  638485       97172   133092      9.0      4280\n",
       "650978  639734       97476   189690      3.0     V4976\n",
       "650979  639735       97476   189690      4.0     56210\n",
       "650980  639736       97476   189690      5.0     30391\n",
       "650981  639737       97476   189690      6.0     33829\n",
       "650982  639738       97476   189690      7.0       311\n",
       "650983  639739       97484   172304      1.0     20280\n",
       "650984  639740       97484   172304      2.0      3485\n",
       "650985  639741       97484   172304      3.0      7843\n",
       "650986  639742       97484   172304      4.0      4019\n",
       "650987  639743       97484   172304      5.0      2720\n",
       "650988  639744       97484   172304      6.0     53081\n",
       "650989  639745       97488   152542      1.0       566\n",
       "650990  639746       97488   152542      2.0     25062\n",
       "650991  639747       97488   152542      3.0      3572\n",
       "650992  639748       97488   152542      4.0     V5867\n",
       "650993  639749       97488   152542      5.0     42731\n",
       "650994  639750       97488   152542      6.0      4019\n",
       "650995  639751       97488   152542      7.0      4280\n",
       "650996  639752       97488   152542      8.0     53081\n",
       "650997  639753       97488   152542      9.0      3051\n",
       "650998  639754       97488   152542     10.0     27800\n",
       "650999  639755       97488   152542     11.0     78820\n",
       "651000  639756       97488   161999      1.0     43411\n",
       "651001  639757       97488   161999      2.0      3485\n",
       "651002  639758       97488   161999      3.0      3484\n",
       "651003  639759       97488   161999      4.0       430\n",
       "651004  639760       97488   161999      5.0     34830\n",
       "651005  639761       97488   161999      6.0     99731\n",
       "651006  639762       97488   161999      7.0     51883\n",
       "651007  639763       97488   161999      8.0      5990\n",
       "651008  639764       97488   161999      9.0     34291\n",
       "651009  639765       97488   161999     10.0     29181\n",
       "651010  639766       97488   161999     11.0     V4588\n",
       "651011  639767       97488   161999     12.0     42731\n",
       "651012  639768       97488   161999     13.0      4019\n",
       "651013  639769       97488   161999     14.0      2724\n",
       "651014  639770       97488   161999     15.0     25000\n",
       "651015  639771       97488   161999     16.0     V5867\n",
       "651016  639772       97488   161999     17.0      4280\n",
       "651017  639773       97488   161999     18.0      3051\n",
       "651018  639774       97488   161999     19.0      7843\n",
       "651019  639775       97488   161999     20.0      0414\n",
       "651020  639776       97488   161999     21.0     30391\n",
       "651021  639777       97488   161999     22.0     E8798\n",
       "651022  639778       97488   161999     23.0     78791\n",
       "651023  639779       97488   161999     24.0     V4986\n",
       "651024  639780       97492   189314      1.0     34680\n",
       "651025  639781       97492   189314      2.0      7843\n",
       "651026  639782       97492   189314      3.0      7455\n",
       "651027  639783       97492   189314      4.0     78194\n",
       "651028  639784       97492   189314      5.0     36840\n",
       "651029  639785       97492   189314      6.0      7813\n",
       "651030  639786       97492   189314      7.0      7820\n",
       "651031  639787       97492   189314      8.0       311\n",
       "651032  639788       97497   168949      1.0      0529\n",
       "651033  639789       97497   168949      2.0      4162\n",
       "651034  639790       97497   168949      3.0     20192\n",
       "651035  639791       97497   168949      4.0       135\n",
       "651036  639792       97497   168949      5.0     27669\n",
       "651037  639793       97497   168949      6.0      5178\n",
       "651038  639794       97497   168949      7.0     42731\n",
       "651039  639795       97497   168949      8.0     V5861\n",
       "651040  639796       97497   168949      9.0     45829\n",
       "651041  639797       97503   188195      1.0      7842\n",
       "651042  639798       97503   188195      2.0     20280\n",
       "651043  639799       97503   188195      3.0     V5869\n",
       "651044  639800       97503   188195      4.0     V1279\n",
       "651045  639801       97503   188195      5.0      5275\n",
       "651046  639802       97503   188195      6.0      5569\n",
       "\n",
       "[651047 rows x 5 columns]"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ICD_diag=pd.read_csv(file_path+\"DIAGNOSES_ICD.csv\")\n",
    "ICD_diag"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "main_diag=ICD_diag.loc[(ICD_diag[\"SEQ_NUM\"]==1)]\n",
    "lab_short_tensor_m=pd.merge(lab_short_tensor,main_diag[[\"HADM_ID\",\"ICD9_CODE\"]],on=\"HADM_ID\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "#Only select the first 3 digits of each ICD9 code.\n",
    "lab_short_tensor_m[\"ICD9_short\"]=lab_short_tensor_m[\"ICD9_CODE\"].astype(str).str[:3]\n",
    "#Check that all codes are 3 digits long.\n",
    "str_len=lab_short_tensor_m[\"ICD9_short\"].str.len()\n",
    "assert(str_len.loc[str_len!=3].count()==0)\n",
    "\n",
    "\n",
    "\n",
    "#Map the ICD codes to their main category and then derive a one hot encoding.\n",
    "#criteria=[lab_short_tensor_m[\"ICD9_short\"].between(\"001\",\"139\"),lab_short_tensor_m[\"ICD9_short\"].between(\"140\",\"239\"),lab_short_tensor_m[\"ICD9_short\"].between(\"240\",\"279\"),lab_short_tensor_m[\"ICD9_short\"].between(\"280\",\"289\"),lab_short_tensor_m[\"ICD9_short\"].between(\"290\",\"319\"),lab_short_tensor_m[\"ICD9_short\"].between(\"320\",\"389\"),lab_short_tensor_m[\"ICD9_short\"].between(\"390\",\"459\"),lab_short_tensor_m[\"ICD9_short\"].between(\"460\",\"519\"),lab_short_tensor_m[\"ICD9_short\"].between(\"520\",\"579\"),lab_short_tensor_m[\"ICD9_short\"].between(\"580\",\"629\"),lab_short_tensor_m[\"ICD9_short\"].between(\"630\",\"679\"),lab_short_tensor_m[\"ICD9_short\"].between(\"680\",\"709\"),lab_short_tensor_m[\"ICD9_short\"].between(\"710\",\"739\"),lab_short_tensor_m[\"ICD9_short\"].between(\"740\",\"759\"),lab_short_tensor_m[\"ICD9_short\"].between(\"760\",\"779\"),lab_short_tensor_m[\"ICD9_short\"].between(\"780\",\"799\"),lab_short_tensor_m[\"ICD9_short\"].between(\"800\",\"999\"),lab_short_tensor_m[\"ICD9_short\"].between(\"E\",\"W\")]\n",
    "#values=[chr(i) for i in range(ord('A'),ord('A')+18)]\n",
    "#lab_short_tensor_m[\"ICD9_label\"]=np.select(criteria,values,0)\n",
    "#hot_encoding=pd.get_dummies(lab_short_tensor_m[\"ICD9_label\"])\n",
    "#lab_short_tensor_m[hot_encoding.columns]=hot_encoding\n",
    "\n",
    "\n",
    "#Finer encoding (3 digits)\n",
    "hot_encodings=pd.get_dummies(lab_short_tensor_m[\"ICD9_short\"])\n",
    "lab_short_tensor_m[hot_encodings.columns]=hot_encodings\n",
    "\n",
    "#Extra time features computation\n",
    "lab_short_tensor_m[\"TIME_SQ\"]=lab_short_tensor_m[\"TIME_STAMP\"]**2\n",
    "\n",
    "#New order of the columns.\n",
    "new_cols=[\"UNIQUE_ID\",\"LABEL_CODE\",\"TIME_STAMP\",\"DEATHTAG\"]+list(hot_encodings.columns)+[\"VALUENUM\"]\n",
    "lab_short_tensor_m=lab_short_tensor_m[new_cols].copy()\n",
    "\n",
    "lab_short_tensor_nocov=lab_short_tensor_m[[\"UNIQUE_ID\",\"LABEL_CODE\",\"TIME_STAMP\",\"DEATHTAG\"]+[\"VALUENUM\"]].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Add a column with the mean and std of each different measurement type and then normalize them.\n",
    "d_mean=dict(lab_short_tensor_nocov.groupby(\"LABEL_CODE\")[\"VALUENUM\"].mean())\n",
    "lab_short_tensor_nocov[\"MEAN\"]=lab_short_tensor_nocov[\"LABEL_CODE\"].map(d_mean)\n",
    "d_std=dict(lab_short_tensor_nocov.groupby(\"LABEL_CODE\")[\"VALUENUM\"].std())\n",
    "lab_short_tensor_nocov[\"STD\"]=lab_short_tensor_nocov[\"LABEL_CODE\"].map(d_std)\n",
    "lab_short_tensor_nocov[\"VALUENORM\"]=(lab_short_tensor_nocov[\"VALUENUM\"]-lab_short_tensor_nocov[\"MEAN\"])/lab_short_tensor_nocov[\"STD\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Standard train-validation split\n",
    "We randomly split the dataset in validation and training set."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Split training_validation_test sets RANDOM DIVISION.\n",
    "\n",
    "df_train,df_val =train_test_split(lab_short_tensor_nocov,test_size=0.2) \n",
    "\n",
    "#Make sure that patients of the validation set have instances in the training set. (same with labels but this should be nearly certain)\n",
    "assert(len(df_val.loc[~df_val[\"UNIQUE_ID\"].isin(df_train[\"UNIQUE_ID\"])].index)==0)\n",
    "assert(len(df_val.loc[~df_val[\"LABEL_CODE\"].isin(df_train[\"LABEL_CODE\"])].index)==0)\n",
    "\n",
    "#Save locally.\n",
    "lab_short_tensor_nocov.to_csv(file_path+\"lab_short_tensor.csv\")\n",
    "df_train.to_csv(file_path+\"lab_short_tensor_train.csv\")\n",
    "df_val.to_csv(file_path+\"lab_short_tensor_val.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Special train-validation split\n",
    "\n",
    "We first randomly split the dataset in training and validation set but then remove all training samples that are located further in time than the validation sample. Concretely it gives :\n",
    "\n",
    "For each validation sample (x,y,t) with x:ID, y:feature and t=time, we remove all training samples (x,y,T) for T>t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Validation ratio : 0.11923245672484525\n",
      "Number of training samples : 1718381\n"
     ]
    }
   ],
   "source": [
    "#Perform a more conservative split. We remove from the training set all further samples (in time) of each validation sample.\n",
    "\n",
    "df_train,df_val =train_test_split(lab_short_tensor_nocov,test_size=0.1) #This operation keeps the right index\n",
    "#We now select the entries of the full dataset wich have common UNIQUE_ID and LABEL CODE with the training samples.\n",
    "flagged_entries=pd.merge(lab_short_tensor_nocov.reset_index(),df_val,how=\"inner\",on=[\"UNIQUE_ID\",\"LABEL_CODE\"]).set_index(\"index\")\n",
    "#The index of entries to remove are the ones which are further in time than the validation samples\n",
    "to_drop_idx=np.intersect1d(flagged_entries.loc[flagged_entries[\"TIME_STAMP_x\"]>flagged_entries[\"TIME_STAMP_y\"]].index,df_train.index)\n",
    "df_train_clean=df_train.drop(to_drop_idx).copy()\n",
    "\n",
    "#UNIT TEST\n",
    "merged_train_clean=pd.merge(df_train_clean,df_val,how=\"inner\",on=[\"UNIQUE_ID\",\"LABEL_CODE\"])\n",
    "assert(len(merged_train_clean.loc[merged_train_clean[\"TIME_STAMP_x\"]>merged_train_clean[\"TIME_STAMP_y\"]].index)==0)\n",
    "\n",
    "#Make sure that patients of the validation set have instances in the training set. (same with labels but this should be nearly certain)\n",
    "assert(len(df_val.loc[~df_val[\"UNIQUE_ID\"].isin(df_train_clean[\"UNIQUE_ID\"])].index)==0)\n",
    "\n",
    "#Training and Validation ratios:\n",
    "print(\"Validation ratio : \"+str(len(df_val.index)/(len(df_val.index)+len(df_train_clean.index))))\n",
    "print(\"Number of training samples : \"+str(len(df_train_clean.index)))\n",
    "\n",
    "#Save locally\n",
    "df_train_clean.to_csv(file_path+\"lab_short_tensor_train_HARD.csv\")\n",
    "df_val.to_csv(file_path+\"lab_short_tensor_val_HARD.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Covariates values of each unique_id\n",
    "\n",
    "We create a dataset storing the value of the covariates for each patient id (UNIQUE_ID)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {},
   "outputs": [],
   "source": [
    "covariates=lab_short_tensor_m.groupby(\"UNIQUE_ID\").first()[list(hot_encodings.columns)]\n",
    "covariates.to_csv(file_path+\"lab_covariates_val.csv\") #save locally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [],
   "source": [
    "cov=pd.read_csv(file_path+\"lab_covariates_val.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[0, 0, 0, ..., 0, 0, 0],\n",
       "       [0, 0, 0, ..., 0, 0, 0],\n",
       "       [0, 0, 0, ..., 0, 0, 0],\n",
       "       ...,\n",
       "       [0, 0, 0, ..., 0, 0, 0],\n",
       "       [0, 0, 0, ..., 0, 0, 0],\n",
       "       [0, 0, 0, ..., 0, 0, 0]], dtype=uint8)"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "covariates.as_matrix()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21377"
      ]
     },
     "execution_count": 174,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "lab_short[\"HADM_ID\"].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
